package com.kla.power.mapper;

import com.kla.power.entity.StationEntity;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

/**
 * <p>
 * 发电站表 Mapper 接口
 * </p>
 *
 * @author WuZH
 * @since 2024-06-02
 */
@Mapper
public interface StationMapper extends BaseMapper<StationEntity> {

    @Select ( value = "<script>SELECT * FROM station_info si " +
            "<trim prefix=\"where\" prefixOverrides=\"and | or\">" +
            "<if test='stationEntity.stationName!=null'> AND si.stationName LIKE CONCAT('%', #{stationEntity.stationName, jdbcType=VARCHAR}, '%') </if>" +
            "<if test='stationEntity.stationType!=null'> AND si.stationType=#{stationEntity.stationType} </if>" +
            "<if test='stationEntity.type!=null'> AND si.type=#{stationEntity.type}</if> " +
            "<if test='stationEntity.merchantId!=null'> AND " +
            "si.merchantId IN (SELECT id\n" +
            "                        FROM (SELECT t1.id,\n" +
            "                                     t1.parentId,\n" +
            "                                     IF(find_in_set(parentId, @pids) > 0, @pids := concat(@pids, ',', id), 0) AS ischild\n" +
            "                              FROM (SELECT id, parentId FROM merchant_info t) t1,\n" +
            "                                   (SELECT @pids:=#{stationEntity.merchantId}) t2 ) t3 where ischild != 0\n" +
            "                                    UNION\n" +
            "                                    SELECT id\n" +
            "                                    FROM merchant_info pi\n" +
            "                                    WHERE pi.id=#{stationEntity.merchantId})" +
            "</if> " +
            "<if test='stationEntity.parentId!=null'> AND si.parentId=#{stationEntity.parentId}</if> " +
            "<if test='stationEntity.type!=null'> AND si.type=#{stationEntity.type}</if> " +
            " AND si.isUsed=1</trim> ORDER BY si.createTime DESC LIMIT #{stationEntity.page}, #{stationEntity.limit}</script>" )
    List<StationEntity> queryList (@Param ( "stationEntity" ) StationEntity stationEntity );


    @Select ( value = "<script>SELECT COUNT(1) FROM station_info si " +
            "<trim prefix=\"where\" prefixOverrides=\"and | or\">" +
            "<if test='stationEntity.stationName!=null'> AND si.stationName LIKE CONCAT('%', #{stationEntity.stationName, jdbcType=VARCHAR}, '%') </if>" +
            "<if test='stationEntity.stationType!=null'> AND si.stationType=#{stationEntity.stationType} </if>" +
            "<if test='stationEntity.type!=null'> AND si.type=#{stationEntity.type}</if> " +
            "AND si.isUsed=1</trim></script>" )
    Integer queryCount ( @Param ( "stationEntity" ) StationEntity stationEntity );

    @Select ( value = "<script>SELECT *,ai.areaName AS provinceName, aii.areaName AS cityName, area.areaName AS countyName, aii.areaCode AS cityCode FROM station_info si LEFT JOIN area_info ai ON si.provinceId = ai.id LEFT JOIN area_info aii on si.cityId = aii.id LEFT JOIN area_info area on si.countyId = area.id WHERE si.id=#{stationEntity.id}</script>")
    StationEntity querySingle ( @Param ( "stationEntity" ) StationEntity stationEntity );


    /**
     * 大屏报表，查询商户下所有电站
     * @param stationEntity
     * @return
     */
    @Select ( value = "<script>SELECT * FROM station_info si " +
            "<trim prefix=\"where\" prefixOverrides=\"and | or\">" +
            "<if test='stationEntity.merchantId!=null'> AND " +
            "si.merchantId IN (SELECT id\n" +
            "                        FROM (SELECT t1.id,\n" +
            "                                     t1.parentId,\n" +
            "                                     IF(find_in_set(parentId, @pids) > 0, @pids := concat(@pids, ',', id), 0) AS ischild\n" +
            "                              FROM (SELECT id, parentId FROM merchant_info t) t1,\n" +
            "                                   (SELECT @pids:=#{stationEntity.merchantId}) t2 ) t3 where ischild != 0\n" +
            "                                    UNION\n" +
            "                                    SELECT id\n" +
            "                                    FROM merchant_info pi\n" +
            "                                    WHERE pi.id=#{stationEntity.merchantId})" +
            "</if> " +
            "<if test='stationEntity.parentId!=null'> AND si.parentId=#{stationEntity.parentId}</if> " +
            " AND si.type=#{stationEntity.type} AND si.isUsed=1</trim></script>" )
    List<StationEntity> dashboardStationReport(@Param ( "stationEntity" ) StationEntity stationEntity);


    /**
     * 按电站所在省份分组查询
     * @param stationEntity
     * @return
     */
    @Select ( value = "select count(1) AS stationNum, ai.areaName, si.stationName\n" +
            "from station_info si\n" +
            "         left join area_info ai on si.provinceId = ai.id\n" +
            "where si.merchantId IN (SELECT id\n" +
            "                                                FROM (SELECT t1.id,\n" +
            "                                                             t1.parentId,\n" +
            "                                                             IF(find_in_set(parentId, @pids) > 0, @pids := concat(@pids, ',', id), 0) AS ischild\n" +
            "                                                      FROM (SELECT id, parentId FROM merchant_info t) t1,\n" +
            "                                                           (SELECT @pids:=#{stationEntity.merchantId}) t2 ) t3 where ischild != 0\n" +
            "                                                            UNION\n" +
            "                                                            SELECT id\n" +
            "                                                            FROM merchant_info pi\n" +
            "                                                            WHERE pi.id=#{stationEntity.merchantId})\n" +
            "group by si.provinceId")
    List<Map<String, Integer>> screenStationNum( @Param ( "stationEntity" ) StationEntity stationEntity);


}
